using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using ThridGroup.ERP.ProductionSystem;
using ThridGroup.ERP.Application.Contracts.DTO;
using ThridGroup.ERP.Application.Contracts.ProductionSystem;
using Volo.Abp.Application.Services;
using Volo.Abp.Domain.Repositories;
using Microsoft.Extensions.Logging;
using Microsoft.EntityFrameworkCore;
using Volo.Abp.EntityFrameworkCore;
using ThridGroup.ERP.EntityFrameworkCore;
using ThridGroup.ERP.ProductionSystem;

namespace ThridGroup.ERP.Application.ProductionSystem
{
    public class MaintenanceplanService : ApplicationService, IMaintenanceplanService
    {
        private readonly IRepository<MaintenanceplanModel, int> _maintenanceplanRepository;
        private readonly ILogger<MaintenanceplanService> _logger;
        private readonly IDbContextProvider<ERPDbContext> _dbContextProvider;

        public MaintenanceplanService(
            IRepository<MaintenanceplanModel, int> maintenanceplanRepository,
            ILogger<MaintenanceplanService> logger,
            IDbContextProvider<ERPDbContext> dbContextProvider)
        {
            _maintenanceplanRepository = maintenanceplanRepository;
            _logger = logger;
            _dbContextProvider = dbContextProvider;
        }

        /// <summary>
        /// 添加点检保养计划
        /// </summary>
        public async Task<int> AddMaintenanceplan(MaintenanceplanDto dto)
        {
            try
            {
                _logger.LogInformation($"开始添加点检保养计划，ProjectCode={dto.ProjectCode}, ProjectName={dto.ProjectName}");

                // 验证输入
                ValidateMaintenanceplanInput(dto);

                // 创建实体
                var entity = new MaintenanceplanModel
                {
                    ProjectCode = dto.ProjectCode,
                    ProjectName = dto.ProjectName,
                    ProjectType = dto.ProjectType,
                    ProjectContent = dto.ProjectContent,
                    Standard = dto.Standard,
                    WhethertoEnable = dto.WhethertoEnable
                };

                _logger.LogInformation($"准备插入数据库：ProjectCode={entity.ProjectCode}, ProjectName={entity.ProjectName}");

                // 插入数据库
                var result = await _maintenanceplanRepository.InsertAsync(entity, true);

                _logger.LogInformation($"点检保养计划添加成功，ID：{result.Id}");
                return result.Id;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"添加点检保养计划失败：{ex.Message}");
                throw new Exception($"添加点检保养计划失败：{ex.Message}", ex);
            }
        }

        /// <summary>
        /// 更新点检保养计划
        /// </summary>
        public async Task<int> UpdateMaintenanceplan(MaintenanceplanDto dto)
        {
            try
            {
                _logger.LogInformation($"开始更新点检保养计划，ID：{dto.Id}, ProjectCode={dto.ProjectCode}, ProjectName={dto.ProjectName}");

                // 验证ID
                if (dto.Id <= 0)
                {
                    throw new ArgumentException("无效的点检保养计划ID");
                }

                // 验证输入
                ValidateMaintenanceplanInput(dto);

                // 获取现有实体
                var entity = await _maintenanceplanRepository.GetAsync(dto.Id);

                if (entity == null)
                {
                    throw new Exception($"未找到ID为{dto.Id}的点检保养计划");
                }

                // 更新实体属性
                entity.ProjectCode = dto.ProjectCode;
                entity.ProjectName = dto.ProjectName;
                entity.ProjectType = dto.ProjectType;
                entity.ProjectContent = dto.ProjectContent;
                entity.Standard = dto.Standard;
                entity.WhethertoEnable = dto.WhethertoEnable;

                _logger.LogInformation($"准备更新数据库，ID：{entity.Id}");

                // 更新数据库
                await _maintenanceplanRepository.UpdateAsync(entity, true);

                _logger.LogInformation($"点检保养计划更新成功，ID：{entity.Id}");
                return entity.Id;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"更新点检保养计划失败：{ex.Message}");
                throw new Exception($"更新点检保养计划失败：{ex.Message}", ex);
            }
        }

        /// <summary>
        /// 删除点检保养计划
        /// </summary>
        public async Task DeleteMaintenanceplan(int id)
        {
            try
            {
                _logger.LogInformation($"开始删除点检保养计划，ID：{id}");

                // 验证ID
                if (id <= 0)
                {
                    throw new ArgumentException("无效的点检保养计划ID");
                }

                // 获取现有实体
                var entity = await _maintenanceplanRepository.GetAsync(id);
                
                if (entity == null)
                {
                    throw new Exception($"未找到ID为{id}的点检保养计划");
                }

                // 删除记录
                _logger.LogInformation($"准备删除数据库记录，ID：{id}");
                await _maintenanceplanRepository.DeleteAsync(entity, true);
                
                _logger.LogInformation($"点检保养计划删除成功，ID：{id}");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"删除点检保养计划失败：{ex.Message}");
                throw new Exception($"删除点检保养计划失败：{ex.Message}", ex);
            }
        }


        /// <summary>
        /// 获取点检保养计划
        /// </summary>
        public async Task<MaintenanceplanDto> GetMaintenanceplan(int id)
        {
            try
            {
                _logger.LogInformation($"开始获取点检保养计划，ID：{id}");

                // 验证ID
                if (id <= 0)
                {
                    throw new ArgumentException("无效的点检保养计划ID");
                }

                // 尝试使用Repository方式获取数据
                try
                {
                    var entity = await _maintenanceplanRepository.GetAsync(id);

                    if (entity == null)
                    {
                        throw new Exception($"未找到ID为{id}的点检保养计划");
                    }


                   var dto = new MaintenanceplanDto
                    {
                        Id = entity.Id,
                        ProjectCode = entity.ProjectCode,
                        ProjectName = entity.ProjectName,
                        ProjectType = entity.ProjectType,
                        ProjectContent = entity.ProjectContent,
                        Standard = entity.Standard,
                        WhethertoEnable = entity.WhethertoEnable
                    };
                    
                    _logger.LogInformation($"成功获取点检保养计划，ID：{id}");
                    return dto;
                }
                catch (Exception repositoryEx)
                {
                    _logger.LogWarning(repositoryEx, $"使用Repository方式获取数据失败，尝试使用原始SQL查询，ID：{id}");
                    
                    // 如果Repository方式失败，使用原始SQL查询
                    var dbContext = await _dbContextProvider.GetDbContextAsync();
                    
                    // 使用原始SQL查询，只选择存在的字段
                    var sql = @"
                        SELECT 
                            ""Id"", 
                            ""ProjectCode"", 
                            ""ProjectName"", 
                            ""ProjectType"", 
                            ""ProjectContent"",
                            ""Standard"",
                            ""WhethertoEnable""
                        FROM ""maintenanceplanModels""
                        WHERE ""Id"" = @id";
                    
                    var connection = dbContext.Database.GetDbConnection();
                    await dbContext.Database.OpenConnectionAsync();
                    
                    using var command = connection.CreateCommand();
                    command.CommandText = sql;
                    
                    var parameter = command.CreateParameter();
                    parameter.ParameterName = "@id";
                    parameter.Value = id;
                    command.Parameters.Add(parameter);
                    
                    using var reader = await command.ExecuteReaderAsync();
                    if (await reader.ReadAsync())
                    {
                        var dto = new MaintenanceplanDto
                        {
                            Id = Convert.ToInt32(reader["Id"]),
                            ProjectCode = reader["ProjectCode"]?.ToString() ?? "",
                            ProjectName = reader["ProjectName"]?.ToString() ?? "",
                            ProjectType = reader["ProjectType"]?.ToString() ?? "",
                            ProjectContent = reader["ProjectContent"]?.ToString() ?? "",
                            Standard = reader["Standard"]?.ToString() ?? "",
                            WhethertoEnable = reader["WhethertoEnable"]?.ToString() ?? ""
                        };
                        
                        _logger.LogInformation($"使用原始SQL成功获取点检保养计划，ID：{id}");
                        return dto;
                    }
                    else
                    {
                        throw new Exception($"未找到ID为{id}的点检保养计划");
                    }
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"获取点检保养计划失败：{ex.Message}");
                throw new Exception($"获取点检保养计划失败：{ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取所有点检保养计划
        /// </summary>
        public async Task<List<MaintenanceplanDto>> GetMaintenanceplans()
        {
            try
            {
                _logger.LogInformation("开始获取所有点检保养计划");

                try
                {
                    var entities = await _maintenanceplanRepository.GetListAsync();
                    
                    // 转换为DTO列表
                    var dtoList = entities.Select(x => new MaintenanceplanDto
                    {
                        Id = x.Id,
                        ProjectCode = x.ProjectCode,
                        ProjectName = x.ProjectName,
                        ProjectType = x.ProjectType,
                        ProjectContent = x.ProjectContent,
                        Standard = x.Standard,
                        WhethertoEnable = x.WhethertoEnable
                    }).ToList();
                    
                    _logger.LogInformation($"成功获取点检保养计划列表，共{dtoList.Count}条记录");
                    return dtoList;
                }
                catch (Exception repositoryEx)
                {
                    _logger.LogWarning(repositoryEx, "使用Repository方式获取数据失败，尝试使用原始SQL查询");
                    
                    // 如果Repository方式失败，使用原始SQL查询
                    var dbContext = await _dbContextProvider.GetDbContextAsync();
                    
                    // 使用原始SQL查询，只选择存在的字段
                    var sql = @"
                        SELECT 
                            ""Id"", 
                            ""ProjectCode"", 
                            ""ProjectName"", 
                            ""ProjectType"", 
                            ""ProjectContent"",
                            ""Standard"",
                            ""WhethertoEnable""
                        FROM ""maintenanceplanModels""";
                    
                    var connection = dbContext.Database.GetDbConnection();
                    await dbContext.Database.OpenConnectionAsync();
                    
                    using var command = connection.CreateCommand();
                    command.CommandText = sql;
                    
                    var dtoList = new List<MaintenanceplanDto>();
                    
                    using var reader = await command.ExecuteReaderAsync();
                    while (await reader.ReadAsync())
                    {
                        var dto = new MaintenanceplanDto
                        {
                            Id = Convert.ToInt32(reader["Id"]),
                            ProjectCode = reader["ProjectCode"]?.ToString() ?? "",
                            ProjectName = reader["ProjectName"]?.ToString() ?? "",
                            ProjectType = reader["ProjectType"]?.ToString() ?? "",
                            ProjectContent = reader["ProjectContent"]?.ToString() ?? "",
                            Standard = reader["Standard"]?.ToString() ?? "",
                            WhethertoEnable = reader["WhethertoEnable"]?.ToString() ?? ""
                        };
                        dtoList.Add(dto);
                    }
                    
                    _logger.LogInformation($"使用原始SQL成功获取点检保养计划列表，共{dtoList.Count}条记录");
                    return dtoList;
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"获取点检保养计划列表失败：{ex.Message}");
                throw new Exception($"获取点检保养计划列表失败：{ex.Message}", ex);
            }
        }

        /// <summary>
        /// 验证点检保养计划输入
        /// </summary>
        private void ValidateMaintenanceplanInput(MaintenanceplanDto dto)
        {
            if (string.IsNullOrWhiteSpace(dto.ProjectCode))
            {
                throw new ArgumentException("项目编码不能为空");
            }

            if (string.IsNullOrWhiteSpace(dto.ProjectName))
            {
                throw new ArgumentException("项目名称不能为空");
            }
        }
    }
} 